package edu.gdin.ecommerce.service.impl;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.annotation.Resource;

import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WritableWorkbook;

import org.springframework.stereotype.Service;

import edu.gdin.ecommerce.dao.AccountDAO;
import edu.gdin.ecommerce.dao.CardBatchDAO;
import edu.gdin.ecommerce.dao.CardDAO;
import edu.gdin.ecommerce.dao.CardRecordDAO;
import edu.gdin.ecommerce.dao.CustomerDAO;
import edu.gdin.ecommerce.dao.InventoryDAO;
import edu.gdin.ecommerce.model.Account;
import edu.gdin.ecommerce.model.Card;
import edu.gdin.ecommerce.model.CardBatch;
import edu.gdin.ecommerce.model.CardRecord;
import edu.gdin.ecommerce.model.Customer;
import edu.gdin.ecommerce.model.Inventory;
import edu.gdin.ecommerce.model.User;
import edu.gdin.ecommerce.service.IAccountService;
import edu.gdin.ecommerce.service.exception.ServiceException;
import edu.gdin.ecommerce.util.DateUtil;
import edu.gdin.ecommerce.util.ExportExcel;
import edu.gdin.ecommerce.util.FilePath;
import edu.gdin.ecommerce.util.Pager;

@Service
public class AccountService implements IAccountService {

	private AccountDAO accountDAO;
	private CardBatchDAO cardBatchDAO;
	private InventoryDAO inventoryDAO;
	private CardDAO cardDAO;
	private CardRecordDAO cardRecordDAO;
	private CustomerDAO customerDAO;
	
	public AccountDAO getAccountDAO() {
		return accountDAO;
	}
	
	@Resource
	public void setAccountDAO(AccountDAO accountDAO) {
		this.accountDAO = accountDAO;
	}

	public CardBatchDAO getCardBatchDAO() {
		return cardBatchDAO;
	}
	@Resource
	public void setCardBatchDAO(CardBatchDAO cardBatchDAO) {
		this.cardBatchDAO = cardBatchDAO;
	}
	
	public InventoryDAO getInventoryDAO() {
		return inventoryDAO;
	}
	@Resource
	public void setInventoryDAO(InventoryDAO inventoryDAO) {
		this.inventoryDAO = inventoryDAO;
	}

	public CardDAO getCardDAO() {
		return cardDAO;
	}
	@Resource
	public void setCardDAO(CardDAO cardDAO) {
		this.cardDAO = cardDAO;
	}

	public CardRecordDAO getCardRecordDAO() {
		return cardRecordDAO;
	}
	@Resource
	public void setCardRecordDAO(CardRecordDAO cardRecordDAO) {
		this.cardRecordDAO = cardRecordDAO;
	}

	public CustomerDAO getCustomerDAO() {
		return customerDAO;
	}
	@Resource
	public void setCustomerDAO(CustomerDAO customerDAO) {
		this.customerDAO = customerDAO;
	}

	//	@Override
	public boolean addAccount(Account account) {
		try {
			accountDAO.insert(account);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}
	

//	@Override
	public Pager queryAccount(String cardNo, String accountName, Integer useType,
			String customerName, String operTime1,String operTime2, Integer cPage) {
		
		return this.accountDAO.queryAccount(cardNo, accountName, useType, customerName,
				operTime1, operTime2, cPage);
	}
	
	
	public Account getAccountById(Integer accountId){
		return this.accountDAO.getAccountById(accountId);
	}
	
	
	public boolean updateAccount(Account account){
		try {
			this.accountDAO.update(account);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

//	@Override
	public Pager getAccountRecoredById(Integer accountId, Integer cpg) {
		return this.accountDAO.getAccountRecordById(accountId, cpg);
	}
	
	/**
	 * 批量开户
	 * @param batchNo
	 * @param useType
	 * @param account
	 * @param user
	 */
	public void batchAddAccount(String batchNo, Integer useType, Account account, User user) {
		if(batchNo == null || batchNo.equals("")) {
			throw new ServiceException("", "请输入卡批次号!");
		}
		CardBatch batch = (CardBatch) this.cardBatchDAO.findByPkForUpdate(CardBatch.class, batchNo);
		if(batch == null) {
			throw new ServiceException("", "不存在该卡批次号!");
		}
		if(batch.getStatus() < 3) {
			throw new ServiceException("", "该批次卡未制卡!");
		}
		if(batch.getStatus() > 3) {
			throw new ServiceException("", "该批次卡存在已使用卡!");
		}
		
		List<Inventory> inventories = (List<Inventory>) this.inventoryDAO.getEntitysByKey(Inventory.class, "batchNo", batchNo);
		Iterator<Inventory> iterator = inventories.iterator();
		while (iterator.hasNext()) {
			
			Inventory in = (Inventory) iterator.next();
			if(in.getStatus() != 3) {
				throw new ServiceException("", "该卡已被使用!");
			}
//			Card card = (Card) this.cardDAO.getEntitysByKey(Card.class, "cardNo", in.getInventoryNo()).get(0);
			List<Card> cards =  (List<Card>) this.cardDAO.getEntitysByKey(Card.class, "cardNo", in.getInventoryNo());
			if(cards != null && cards.size() != 0) {
				throw new ServiceException("", "该卡已被使用!");
			}
			List<CardRecord> records = (List<CardRecord>) this.cardRecordDAO.getEntitysByKey(CardRecord.class, "cardNo", in.getInventoryNo());
			if(records != null && records.size() != 0) {
				throw new ServiceException("", "该卡已被使用!");
			}
			
			Date now = DateUtil.now();
			
			Customer customer = new Customer();
			customer.setName("C" + in.getInventoryNo());
			customer.setMobile("00000000000");
			customer.setAreaProvince("");
			customer.setAreaCity("");
			customer.setAreaDistrict("");
			customer.setCertificateType(1);
			customer.setCertificateNo("00000000000000000");
			customer.setCreateTime(now);
			this.customerDAO.insert(customer);
			
			Account acc = new Account();
			acc.setName("A" + in.getInventoryNo());
			acc.setStatus(0); // 0 = 冻结 1 = 正常 
			acc.setBalance(0D);
			acc.setSingleExpense(account.getSingleExpense()); // ---
			acc.setCreditAmount(account.getCreditAmount()); // ---
			acc.setCurrency(1); // 1 = 人民币  2 = 港币  3 = 澳币
			acc.setScore(account.getScore());
			acc.setBalanceWarn(0); // 0 = 否  1 = 是
			acc.setCreateTime(now);
			acc.setAccountDesc(account.getAccountDesc());
			acc.setCustomer(customer);
			this.accountDAO.insert(acc);
			
			Card card = new Card();
			card.setCardNo(in.getInventoryNo());
			card.setPassword(in.getPassword());
			card.setStatus(2); // 1 = 正常        2 = 未激活   3 = 已冻结    4 = 已挂失   5 = 已注销    (6 = 补卡未激活    7 = 已补卡使用)
			card.setUseType(useType);
			card.setMaterialType(in.getMaterialType());
			card.setCreateTime(now);
			card.setBatchNo(in.getBatchNo());
			card.setMark(1); // 1 = 主卡  2 = 附卡
			card.setCreateUser(user.getAccount());
			
			card.setAccount(acc);
			card.setCardIssuer(in.getCardIssuer());
			this.cardDAO.insert(card);
			
			in.setStatus(4);
			this.inventoryDAO.update(in);
			if(batch.getStatus() != 4) {
				batch.setStatus(4);
				this.cardBatchDAO.update(batch);
			}
		}
		
	}
	
	/**
	 * 导入开户模板
	 * @return
	 */
	public String importAddFile() {
		String sheetTitle = "导入开户模板";
		String fileName = sheetTitle + ".xls";
		String downPath = FilePath.downloadPath();
		
		File file1 = new File(downPath);
		if(!file1.isDirectory()) {
			file1.mkdir();
		}
		
		File file2 = new File(downPath + fileName);
		if(file2.exists()) {
			file2.delete();
		}
		
		// 声明Excel文件
		WritableWorkbook workBook;
		try {
			file2.createNewFile();
			workBook = Workbook.createWorkbook(file2);
			ExportExcel.importAccountFile(workBook, sheetTitle);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return FilePath.relativePath() + fileName;
	}
	
	/**
	 * 导入开户
	 * @param importFile
	 * @param user
	 */
	@SuppressWarnings("unchecked")
	public String importAddAccount(String importFile, User user) {
		String msg = null;
		Workbook workbook = null;
		int endRow = 2;
		try {
			InputStream is = new FileInputStream(importFile);
			workbook = Workbook.getWorkbook(is);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Sheet sheet = workbook.getSheet(0);
		
		if(sheet == null) {
			throw new ServiceException("", "上传文件内容错误!");
		}
		if(sheet.getColumns() != 21) {
			throw new ServiceException("", "上传文件格式不正确!");
		}
		
		for (int i = 2; i < sheet.getRows(); i++) {
			if(sheet.getCell(0, i).getType() == CellType.EMPTY) {
				endRow = i;
				System.out.println("endRow:" + endRow);
				break;
			}
		}
			
		for (int i = 2; i < endRow; i++) {
			if(sheet.getCell(0, i).getType() == CellType.EMPTY || sheet.getCell(0, i).getContents().trim().equals("") ||
					sheet.getCell(2, i).getType() == CellType.EMPTY || sheet.getCell(2, i).getContents().trim().equals("") ||
					sheet.getCell(7, i).getType() == CellType.EMPTY || sheet.getCell(7, i).getContents().trim().equals("") ||
					sheet.getCell(8, i).getType() == CellType.EMPTY || sheet.getCell(8, i).getContents().trim().equals("") ||
					sheet.getCell(9, i).getType() == CellType.EMPTY || sheet.getCell(9, i).getContents().trim().equals("") ||
					sheet.getCell(10, i).getType() == CellType.EMPTY || sheet.getCell(10, i).getContents().trim().equals("") ||
					sheet.getCell(11, i).getType() == CellType.EMPTY || sheet.getCell(11, i).getContents().trim().equals("") ||
					sheet.getCell(13, i).getType() == CellType.EMPTY || sheet.getCell(13, i).getContents().trim().equals("") ||
					sheet.getCell(14, i).getType() == CellType.EMPTY || sheet.getCell(14, i).getContents().trim().equals("") ||
					sheet.getCell(15, i).getType() == CellType.EMPTY || sheet.getCell(15, i).getContents().trim().equals("") ||
					sheet.getCell(17, i).getType() == CellType.EMPTY || sheet.getCell(17, i).getContents().trim().equals("") ||
					sheet.getCell(19, i).getType() == CellType.EMPTY || sheet.getCell(19, i).getContents().trim().equals("") ||
					sheet.getCell(20, i).getType() == CellType.EMPTY || sheet.getCell(20, i).getContents().trim().equals("")) {
				throw new ServiceException("", "上传文件存在空的必填项，请完善导入数据!");
			}
		}
		for (int i = 2; i < endRow; i++) {
			String cardNo = sheet.getCell(19, i).getContents().trim();
			if(cardNo == null || cardNo.equals("")) {
				throw new ServiceException("", "第" + (int)(i+1) + "行卡号不能为空!");
			}
			
			Inventory in = (Inventory) this.inventoryDAO.findByPk(Inventory.class, cardNo);
			if(in == null) {
				throw new ServiceException("", "第" + (int)(i+1) + "行卡号不存在!");
			}
			in = (Inventory) this.inventoryDAO.findByPkForUpdate(Inventory.class, cardNo); // 若不存在此卡，该方法有异常!
			if(in.getStatus() != 3) {
				throw new ServiceException("", "第" + (int)(i+1) + "行卡号未制卡或已使用!");
			}
			
			List<Card> cards =  (List<Card>) this.cardDAO.getEntitysByKey(Card.class, "cardNo", in.getInventoryNo());
			if(cards != null && cards.size() != 0) {
				throw new ServiceException("", "第" + (int)(i+1) + "行卡号已被使用!");
			}
			List<CardRecord> records = (List<CardRecord>) this.cardRecordDAO.getEntitysByKey(CardRecord.class, "cardNo", in.getInventoryNo());
			if(records != null && records.size() != 0) {
				throw new ServiceException("", "第" + (int)(i+1) + "行卡号已被使用!");
			}
			
			
			Date now = DateUtil.now();
			
			Customer customer = new Customer();
			customer.setName(sheet.getCell(0, i).getContents().trim());
			customer.setBirthday(sheet.getCell(1, i).getType() == CellType.EMPTY ? null : DateUtil.parseDate(sheet.getCell(1, i).getContents().trim()));
			customer.setMobile(sheet.getCell(2, i).getContents().trim());
			customer.setTel(sheet.getCell(3, i).getType() == CellType.EMPTY ? null : sheet.getCell(3, i).getContents().trim());
			customer.setEmail(sheet.getCell(4, i).getType() == CellType.EMPTY ? null : sheet.getCell(4, i).getContents().trim());
			customer.setAddress(sheet.getCell(5, i).getType() == CellType.EMPTY ? null : sheet.getCell(5, i).getContents().trim());
			customer.setPostcode(sheet.getCell(6, i).getType() == CellType.EMPTY ? null : sheet.getCell(6, i).getContents().trim());
			customer.setAreaProvince(sheet.getCell(7, i).getContents().trim());
			customer.setAreaCity(sheet.getCell(8, i).getContents().trim());
			customer.setAreaDistrict(sheet.getCell(9, i).getContents().trim());
			customer.setCertificateType(Integer.parseInt(sheet.getCell(10, i).getContents().trim()));
			String certificateNo = sheet.getCell(11, i).getContents().trim();
			if(this.customerDAO.isExist(Customer.class, "certificateNo", certificateNo)) {
				throw new ServiceException("", "第" + (int)(i+1) + "行证件号已存在，无法添加客户!");
			}
			customer.setCertificateNo(certificateNo);
			customer.setCustomerDesc(sheet.getCell(12, i).getType() == CellType.EMPTY ? null : sheet.getCell(12, i).getContents().trim());
			customer.setCreateTime(now);
			this.customerDAO.insert(customer);
			
			
			Account acc = new Account();
			acc.setName(sheet.getCell(13, i).getContents().trim());
			acc.setStatus(1); // 0 = 冻结 1 = 正常 
			acc.setBalance(0D);
			acc.setSingleExpense(Double.parseDouble(sheet.getCell(14, i).getContents().trim())); 
			acc.setCreditAmount(Double.parseDouble(sheet.getCell(15, i).getContents().trim())); 
			acc.setCurrency(1); // 1 = 人民币  2 = 港币  3 = 澳币
			acc.setScore(sheet.getCell(16, i).getType() == CellType.EMPTY ? 0 : Integer.parseInt(sheet.getCell(16, i).getContents().trim()));
			acc.setBalanceWarn(Integer.parseInt(sheet.getCell(17, i).getContents().trim())); // 0 = 否  1 = 是
			acc.setCreateTime(now);
			acc.setAccountDesc(sheet.getCell(18, i).getType() == CellType.EMPTY ? null : sheet.getCell(18, i).getContents().trim());
			acc.setCustomer(customer);
			this.accountDAO.insert(acc);
			
			Card card = new Card();
			card.setCardNo(in.getInventoryNo());
			card.setPassword(in.getPassword());
			card.setStatus(2); // 1 = 正常        2 = 未激活   3 = 已冻结    4 = 已挂失   5 = 已注销    (6 = 补卡未激活    7 = 已补卡使用)
			card.setUseType(Integer.parseInt(sheet.getCell(20, i).getContents().trim()));
			card.setMaterialType(in.getMaterialType());
			card.setCreateTime(now);
			card.setBatchNo(in.getBatchNo());
			card.setMark(1); // 1 = 主卡  2 = 附卡
			card.setCreateUser(user.getAccount());
			
			card.setAccount(acc);
			card.setCardIssuer(in.getCardIssuer());
			this.cardDAO.insert(card);
			
			in.setStatus(4);
			this.inventoryDAO.update(in);
			CardBatch batch = (CardBatch) this.cardBatchDAO.findByPkForUpdate(CardBatch.class, in.getBatchNo());
			if(batch.getStatus() != 4) {
				batch.setStatus(4);
				this.cardBatchDAO.update(batch);
			}
		}
		
		msg = "import " + (int)(endRow - 2) + " Accounts successfully!";
		return msg;
	}
	
	
	
}
